/*
 * Copyright(C) 2010 Luvina Software Company
 *
 * TestDaoImpl.java, Oct 29, 2010 ntmhuong
 */
package net.luvina.manageuser.dao.impl;

import java.util.List;

import net.luvina.manageuser.dao.TestDao;
import net.luvina.manageuser.dto.TestDto;
import net.luvina.manageuser.hibernate.User;
import net.luvina.manageuser.hibernate.UserGrp;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * TestDaoImpl
 *
 * @author ntmhuong
 *
 */
@Repository("testDao")
public class TestDaoImpl extends GenericDaoImpl<User, Integer> implements TestDao {
	public TestDaoImpl() {
		super(User.class);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TestDao#findUser()
	 */
	@SuppressWarnings("unchecked")
	public List<TestDto> findUser() {
		/*
		 * HibernateCallback callback = new HibernateCallback() { public Object
		 * doInHibernate(Session session) throws HibernateException { Query
		 * query = session.createQuery("FROM User us " +
		 * "INNER JOIN FETCH us.userGrp ug "); return (List<User>) query.list();
		 * } }; return (List<User>) getHibernateTemplate().execute(callback);
		 */
		HibernateCallback callback = new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				StringBuilder sqlCommand = new StringBuilder("");
				sqlCommand = sqlCommand.append(" SELECT  ");
				sqlCommand = sqlCommand.append(" u.user_id as userId, ");
				sqlCommand = sqlCommand.append(" u.user_name as userName, ");
				sqlCommand = sqlCommand.append(" g.user_grp_id as userGrpId, ");
				sqlCommand = sqlCommand.append(" g.user_grp_name as userGrpName ");
				sqlCommand = sqlCommand.append(" FROM tbl_user as u ");
				sqlCommand = sqlCommand.append(" INNER JOIN user_grp as g ");
				sqlCommand = sqlCommand.append(" ON u.user_grp_id = g.user_grp_id ");
				System.out.println("sqlCommand = " + sqlCommand.toString());
				Query query = session.createSQLQuery(sqlCommand.toString()).addScalar("userId", Hibernate.INTEGER).addScalar("userName",
						Hibernate.STRING).addScalar("userGrpId", Hibernate.INTEGER).addScalar("userGrpName", Hibernate.STRING).setResultTransformer(
						Transformers.aliasToBean(TestDto.class));
				// query.addScalar();
				return (List<TestDto>) query.list();

			}
		};
		return (List<TestDto>) getHibernateTemplate().execute(callback);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TestDao#getGroupList()
	 */
	@SuppressWarnings("unchecked")
	public List<UserGrp> getGroupList() {
		HibernateCallback callback = new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery("FROM UserGrp ");
				return (List<UserGrp>) query.list();
			}
		};
		return (List<UserGrp>) getHibernateTemplate().execute(callback);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TestDao#getListUser(int, int, int,
	 * java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public List<User> getListUser(final int offset, final int limit, final int userGrpId, final String userName) {
		HibernateCallback callback = new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				StringBuilder sqlBuilder = new StringBuilder();
				sqlBuilder.append(" FROM User u ");
				sqlBuilder.append(" INNER JOIN FETCH u.userGrp ug ");
				if (userGrpId > 0 && (userName != null && userName != "")) {
					sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
					sqlBuilder.append(" AND u.userName LIKE :userName");
				} else {
					if (userName != null && userName != "") {
						sqlBuilder.append(" WHERE u.userName LIKE :userName");
					}
					if (userGrpId > 0) {
						sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
					}
				}
				Query query = session.createQuery(sqlBuilder.toString());
				query.setFirstResult(offset);
				query.setMaxResults(limit);
				if (userGrpId > 0 && (userName != null && userName != "")) {
					query.setString("userGrpId", String.valueOf(userGrpId));
					query.setString("userName", "%" + userName + "%");
				} else {
					if (userName != null && userName != "") {
						query.setString("userName", "%" + userName + "%");
					}
					if (userGrpId > 0) {
						query.setString("userGrpId", String.valueOf(userGrpId));
					}
				}
				return (List<User>) query.list();
			}
		};
		return (List<User>) getHibernateTemplate().execute(callback);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TestDao#getTotalRecords(int,
	 * java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public int getTotalRecords(final int userGrpId, final String userName) {
		HibernateCallback callback = new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				StringBuilder sqlBuilder = new StringBuilder();
				sqlBuilder.append(" FROM User u ");
				sqlBuilder.append(" INNER JOIN FETCH u.userGrp ug ");
				if (userGrpId > 0 && (userName != null && userName != "")) {
					sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
					sqlBuilder.append(" AND u.userName LIKE :userName");
				} else {
					if (userName != null && userName != "") {
						sqlBuilder.append(" WHERE u.userName LIKE :userName");
					}
					if (userGrpId > 0) {
						sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
					}
				}
				Query query = session.createQuery(sqlBuilder.toString());

				if (userGrpId > 0 && (userName != null && userName != "")) {
					query.setString("userGrpId", String.valueOf(userGrpId));
					query.setString("userName", "%" + userName + "%");
				} else {
					if ((userName != null && userName != "")) {
						query.setString("userName", "%" + userName + "%");
					}
					if (userGrpId > 0) {
						query.setString("userGrpId", String.valueOf(userGrpId));
					}
				}
				List<User> userList = query.list();
				if (userList != null && userList.size() > 0) {
					return userList.size();
				} else {
					return 0;
				}
			}
		};
		return (Integer) getHibernateTemplate().execute(callback);
	}
}